﻿---------------------------------------------------Phantom-----------------------------------------------
------------------------------------------------------------------------------------------------------------------
-------------------------------- Transaction 

create proc locdocgia
as
begin 
begin tran
set tran isolation level serializable
declare @madocgia smallint,@tong int
declare cur cursor dynamic for select ma_docgia from DocGia where year(NgaySinh) >'1991'
open cur
set @tong = (select count(distinct ma_docgia) from DocGia where year(NgaySinh) >'1991')
print N'Tong so nguoi' + cast(@tong as char(3))
print N'Danh sach'
print'-----------------------------------'
fetch next from cur into @madocgia
while(@@FETCH_STATUS=0)
begin
	print @madocgia
	waitfor delay'00:00:05'
	fetch next from cur into @madocgia
end
	close cur
	deallocate cur
commit tran
end

---------------------
create proc sp_ThemNguoiLon @ho nvarchar(15), @tenlot nvarchar(1), @ten nvarchar(15), @ngaysinh smalldatetime,--thang/ngay/nam
												@sonha nvarchar(15), @duong nvarchar(63), @quan nvarchar(2), @dienthoai nvarchar(13)
as
begin
		if(year(getdate()) -year(@ngaysinh) < 18)
			begin
					print N'Khong đu tuoi!!!'
					return;
			end
		else
			begin
					declare @temp_1 int, @flag int
					set @flag = 1
					declare @cur_1 cursor
					set @cur_1 = cursor for( 
														select dg.ma_docgia
														from DocGia dg
													)
					open @cur_1
						fetch next from @cur_1 into @temp_1
						while @@fetch_status = 0
								begin
										if(@temp_1 - @flag >0)
											begin
												break
											end
										set @flag = @flag + 1;
										fetch next from @cur_1 into @temp_1
								end
						--quy dinh: th? d?c gi? s? d?ng 12 tháng
						declare @han_sd smalldatetime
						set @han_sd = dateadd(yy,1,getdate())
						
						insert into DocGia(ma_docgia,ho,tenlot,ten,NgaySinh)values(@flag,@ho,@tenlot,@ten,@ngaysinh)
						insert into NguoiLon(ma_docgia,sonha,duong,quan,dienthoai,han_sd) values(@flag,@sonha,@duong,@quan,@dienthoai,@han_sd)
						print N'Them thanh cong!!!'
					close @cur_1
					deallocate @cur_1
			end
end
-------------------------------------Tran 1
select * from DocGia
exec sp_ThemNguoiLon N'Truong', N'H', N'Chan', '2/4/1994', '79','avc','2',null
select * from DocGia
-------------------------------------Tran 2
exec locdocgia 

select * from DocGia
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
---------------------------------------------------Unrepeatableread-----------------------------------------------
------------------------------------------------------------------------------------------------------------------
-------------------------------- Transaction 
create proc sp_ChoMuon @isbn int,@ma int
as
begin tran
set tran isolation level read uncommitted
begin try
	
		if(not exists (select * from CuonSach where Ma_CuonSach = @ma and isbn=@isbn))
		begin
			print @ma + N'Không tồn tại'
			rollback tran
			return
		end
		if(not exists(select * from CuonSach where Ma_CuonSach = @ma and TinhTrang = 'Y' and isbn=@isbn))
		begin
			print N'Không thể mượn'
			rollback tran
			return
		end
		waitfor delay '00:00:20'
		if (exists(select * from CuonSach where Ma_CuonSach = @ma and TinhTrang = 'Y' and isbn=@isbn))
		begin
			print N'Cho mượn thành công'
		end	
	end try
	begin catch
		raiserror (N'Lỗi',16,1)
		rollback tran
		return
	end catch
commit tran
create proc sp_CapNhat @isbn int,@ma int,@bool char
as
begin tran
	begin try
		update CuonSach set TinhTrang=@bool where Ma_CuonSach=@ma and isbn=@isbn
	end try
	begin catch
		raiserror (N'Lỗi',16,1)
		rollback tran
		return
	end catch
commit tran
--------------------------------------Tran 1
select * from CuonSach where isbn=1 and Ma_CuonSach=3 and TinhTrang='Y'
exec sp_ChoMuon 1,3
select * from CuonSach where isbn=1 and Ma_CuonSach=3 and TinhTrang='Y'
--------------------------------------Tran 2
select * from CuonSach where isbn=1 and Ma_CuonSach=3 and TinhTrang='N'
exec sp_CapNhat 1,3,'N'
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
---------------------------------------------------Deadlock-----------------------------------------------
------------------------------------------------------------------------------------------------------------------
-------------------------------- Transaction A:
begin tran
update Muon set ngayGio_muon='1999-06-06' where isbn=1 and ma_cuonsach=3
waitfor delay '0:0:5' --wait for 5ms
update DocGia set ho=N'Võ' where ma_docgia=1
commit tran
-------------------------------- Transaction B:
begin tran
set tran isolation level repeatable read
update DocGia set ho=N'Phạm' where ma_docgia=1
waitfor delay '0:0:5' --wait for 5ms
update Muon set ngayGio_muon='2000-07-07' where isbn=1 and ma_cuonsach=3
commit tran

---------------------------------------FIX---------------------------------------------------------------
-------------------------------- Transaction A:
DECLARE @RetryCounter INT
SET @RetryCounter = 1
RETRY: -- gan nhan RETRY
BEGIN TRANSACTION
BEGIN TRY

	set tran isolation level repeatable read
	update Muon set ngayGio_muon='1999-06-06' where isbn=1 and ma_cuonsach=3
	waitfor delay '0:0:5' --cho 5ms
	update DocGia set ho=N'Võ' where ma_docgia=1

	COMMIT TRANSACTION
END TRY
BEGIN CATCH
	PRINT 'Deadlock xay ra, Rollback Transaction'
	ROLLBACK TRANSACTION
	DECLARE @DoRetry bit; -- quyet dinh thuc hien lai trans hay khong ?
	DECLARE @ErrorMessage varchar(500)
	SET @doRetry = 0;
	SET @ErrorMessage = ERROR_MESSAGE()
	IF ERROR_NUMBER() = 1205 -- Neu deadlock xay ra
	BEGIN
		SET @doRetry = 1; -- Set @doRetry =1 khi neu loi la deadlock
	END
	IF @DoRetry = 1
	BEGIN
		SET @RetryCounter = @RetryCounter + 1 -- tang Retry Counter len 1
		IF (@RetryCounter > 3) -- neu deadlock xay ra 3 lan
		BEGIN
		
			RAISERROR(@ErrorMessage, 18, 1) -- xuat thong bao la deadlock da xay ra 3 lan
		END
		ELSE -- neu trans chua thuc hien qua 3 lan
		BEGIN
			WAITFOR DELAY '00:00:5' -- cho 5 ms
			GOTO RETRY	-- thuc hien lai trans
		END
	END
	ELSE
	BEGIN
		RAISERROR(@ErrorMessage, 18, 1)
	END
END CATCH
-------------------------------- Transaction B:
DECLARE @RetryCounter INT
SET @RetryCounter = 1
RETRY: -- gan nhan RETRY
BEGIN TRANSACTION
BEGIN TRY

	set tran isolation level repeatable read
	update DocGia set ho=N'Phạm' where ma_docgia=1
	waitfor delay '0:0:5' --cho 5 ms
	update Muon set ngayGio_muon='2000-07-07' where isbn=1 and ma_cuonsach=3

	COMMIT TRANSACTION
END TRY
BEGIN CATCH
	PRINT 'Rollback Transaction'
	ROLLBACK TRANSACTION
	DECLARE @DoRetry bit; -- quyet dinh co lam lai transaction hay khong ?
	DECLARE @ErrorMessage varchar(500)
	SET @doRetry = 0;
	SET @ErrorMessage = ERROR_MESSAGE()
	IF ERROR_NUMBER() = 1205 -- Neu loi xay ra la deadlock
	BEGIN
		SET @doRetry = 1; -- Set @doRetry =1 cho Deadlock
	END
	IF @DoRetry = 1
	BEGIN
		SET @RetryCounter = @RetryCounter + 1 -- tang Retry Counter len 1
		IF (@RetryCounter > 3) -- kiem tra neu deadlock xay ra 3 lan thi khong lam nua
		BEGIN
			RAISERROR(@ErrorMessage, 18, 1) -- xuat thong bao loi
				
		END
		ELSE
		BEGIN
			WAITFOR DELAY '00:00:5' -- cho 5 ms
			GOTO RETRY	-- thuc hien lai trans
		END
	END
	ELSE
	BEGIN
		RAISERROR(@ErrorMessage, 18, 1)
	END
END CATCH